﻿CREATE PROCEDURE FC_InformeForeCast1
as
--DECLARE @InfoForeCast TABLE(
--	[IdCustomer] [int] NOT NULL,
--	[IdSegmentPL] [int] NOT NULL,
--	[IdProduct] [int] NOT NULL,
--	[Codigo] [varchar](20) NULL,
--	[CodProduct] [varchar](50) NULL,
--	[IdPeriod] [int] NOT NULL,
--	[IdEntityF] [int] NOT NULL,
--	[Period] [varchar](20) NULL,
--	[DesProduct] [varchar](150) NULL,
--	[VolumeForeCast] [float] NULL,
--	[CostoForeCast] [float] NULL,
--	[MonedaCostoForeCast] [varchar](20) NULL,
--	[VentaForeCast] [float] NULL,
--	[MonedaVentaForeCast] [varchar](20) NULL,	
--	[VolumenActual] [float] NULL,
--	[CostoActual] [float] NULL,
--	[VentaActual] [float] NULL)
	
declare   @IdProduct int,
		  @IdCustomer int,
		  @IdSegment int,
		  @CodProduct varchar(50),
		  @DesProduct varchar(100)
		  
DECLARE ActualizarCodInteres_Cursor CURSOR
FOR
	SELECT A.IdProduct, A.IdCustomer, C.IdSegment, B.CodProduct, B.DesProduct  
	FROM FC_FORECAST A 
	LEFT JOIN FC_Product B ON A.IdProduct = B.IdProduct
	LEFT JOIN FC_SegmentPL C ON B.IdSegmentPL =	C.IdSegmentPL
	LEFT JOIN FC_Segment D ON C.IdSegment = D.IdSegment 
	GROUP BY A.IdProduct, A.IdCustomer, C.IdSegment, B.CodProduct, B.DesProduct
	
	DELETE FROM InfoForeCast
		
	OPEN ActualizarCodInteres_Cursor
		FETCH ActualizarCodInteres_Cursor 
		INTO @IdProduct, @IdCustomer, @IdSegment, @CodProduct, @DesProduct
		
	WHILE (@@FETCH_STATUS = 0)	
		BEGIN		
			INSERT INTO InfoForeCast 
			SELECT AA.IdCustomer, @IdSegment AS IdSegmentPL, coalesce(AA.IdProduct,@IdProduct) as IdProduct, AA.Codigo, COALESCE(AA.CodProduct,@CodProduct) AS CodProduct, AA.IdPeriod, AA.IdEntityF, 
			substring(convert(varchar(7),AA.Period),1,4)+'-'+substring(convert(varchar(7),AA.Period),5,2) as Period, isnull(AA.DesProduct,@DesProduct) as DesProduct, ISNULL(AA.Volume,0) AS VolumeForeCast,
			(ISNULL(AA.Volume,0)* isnull(AA.ValuePC,0)) as CostoForeCast, isnull(AA.CurrencyPC,'') as MonedaCostoForeCast, (ISNULL(AA.Volume,0) * isnull(AA.ValuesSP,0)) as VentaForeCast, isnull(AA.CurrencySP,'') as MonedaVentaForeCast, 
			ISNULL(BB.Cantidad,0) AS VolumenActual, ISNULL(BB.Costo,0) AS CostoActual, ISNULL(BB.Monto,0) as VentaActual 
			FROM
				(SELECT F.IdForecast, C.IdCustomer, SE.IdSegmentPL, PR.IdProduct, PR.CodProduct, C.Codigo, P.IdPeriod, P.IdEntityF, P.Period, P.DateClose, ISNULL(F.Volume,'') AS Volume, PR.DesProduct,
					CASE WHEN P.DateClose < DATEADD(MONTH, 2, GETDATE()) THEN 0 ELSE 1 END ACTIVE,
					PC.Value as ValuePC, PC.Currency as CurrencyPC, SP.Value as ValuesSP, SP.Currency as CurrencySP
				FROM FC_Period P 
					 LEFT JOIN FC_Customer C ON P.IdEntityF = C.IdEntityF  
					 LEFT JOIN FC_Forecast F ON F.IdPeriod = P.IdPeriod AND F.IdCustomer = C.IdCustomer and F.IdProduct = @IdProduct
					 LEFT JOIN FC_Product PR ON F.IdProduct = PR.IdProduct
					 LEFT JOIN FC_SegmentPL SE ON SE.IdSegmentPL = PR.IdSegmentPL	
					 LEFT JOIN FC_PriceCost PC ON PR.IdEntityF = PC.IdEntityF AND PR.IdProduct = PC.IdProduct
					 LEFT JOIN FC_SalePrice SP ON F.IdCustomer = SP.IdCustomer AND F.IdProduct = SP.IdProduct
				WHERE 
  					((P.DateClose > DATEADD(MONTH, -10, GETDATE()) AND P.DateClose < DATEADD(MONTH, 14, GETDATE())))
  					/*
  					((@Activos = 1) or ((P.DateClose > DATEADD(MONTH, -10, GETDATE()) AND P.DateClose < DATEADD(MONTH, 2, GETDATE())))) and
  					((@Activos = 0) or ((P.DateClose > DATEADD(MONTH, 2, GETDATE()) AND P.DateClose < DATEADD(MONTH, 14, GETDATE()))))  
  					*/ 
  					) AA 
  					LEFT JOIN DetalleVentas_LatamFinal BB ON BB.IdCliente = AA.Codigo AND BB.CODIGOPRODUCTO = @CodProduct  
  						AND BB.Agno = convert(int,SUBSTRING(convert(varchar(6),AA.Period),1,4)) 
						AND BB.Mes = convert(int,SUBSTRING(convert(varchar(6),AA.Period),5,2))

			WHERE AA.IdCustomer IN (select IdCustomer from FC_Forecast where IdProduct = @IdProduct AND IdCustomer = @IdCustomer)	
					--IdProduct = @IdProduct AND IdCustomer = @IdCustomer
			order by AA.Codigo	
			
			FETCH ActualizarCodInteres_Cursor 
			INTO @IdProduct, @IdCustomer, @IdSegment, @CodProduct, @DesProduct
		END 

	CLOSE ActualizarCodInteres_Cursor
	DEALLOCATE ActualizarCodInteres_Cursor
	
--select * from InfoForeCast	